﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using YSC_Core.BLL.Domain.BackstageModels;
using YSC_Core.BLL.Domain.CommonModels;
using YSC_Core.BLL.Domain.Entity;
using YSC_Core.BLL.Domain.Utils;
using YSC_Core.BLL.Domain.ViewModels;
using YSC_Core.BLL.Repository;
using YSC_OpenPlatform_API.Entity;
using YSC_Services.BLL.Services.Interfaces;

namespace YSC_Services.BLL.Services.Implementations
{
    /// <summary>
    /// 综合价格实现
    /// </summary>
    public class ComprehensivePriceBackstageServices : IComprehensivePriceBackstageServices
    {
        private IEfRepository _rep;

        public ComprehensivePriceBackstageServices(IEfRepository rep)
        {
            this._rep = rep;
        }

        /// <summary>
        /// 市场价格(每日)
        /// </summary>
        /// <param name="index"></param>
        /// <param name="size"></param>
        /// <param name="areName"></param>
        /// <param name="dateTime"></param>
        /// <param name="sasearch"></param>
        /// <returns></returns>
        public PaginationModel MarketDailyPriceAll(int index, int size, string areName, string dateTime, string sasearch)
        {
            if (string.IsNullOrEmpty(dateTime))
                dateTime = "2019-9-16";
            // dateTime = DateTime.Now.ToString();

            if (index == 0)
            {
                index = 1; size = 10;
            }


            var marketUnitDailyAll = from a in _rep.Where<MarketPrice>(p => p.Status == 0 && p.CreateTime == Convert.ToDateTime(dateTime))
                                     join u in _rep.Where<YscUser>(p => p.Status == 0) on a.UserId equals u.Id
                                     join m in _rep.Where<MarketUnit>(p => p.Status == 0) on a.MarketId equals m.Id
                                     select new
                                     {
                                         a.UserId,
                                         u.UserName,
                                         MarketId = m.Id,
                                         UnitAddress = m.Address,
                                         m.MarketName,
                                         a.CreateTime
                                     };

            var marketUnitDailyList = marketUnitDailyAll
                .OrderByDescending(p => p.CreateTime)
                .WhereIf(p => p.MarketName.Contains(sasearch) || p.UserName.Contains(sasearch), string.IsNullOrEmpty(sasearch) == false)
                .GroupBy(p => new
                {
                    p.UserName,
                    p.UserId,
                    p.MarketId,
                    p.UnitAddress,
                    p.MarketName,
                    Day = UtilsHelp.Condition(Convert.ToDateTime(p.CreateTime), "Day")
                })
                .Select(p => new
                {
                    p.Key.Day,
                    p.Key.UserName,
                    p.Key.UserId,
                    p.Key.MarketId,
                    p.Key.UnitAddress,
                    p.Key.MarketName
                });

            var models = marketUnitDailyList.ToPaginationList(index, size).ToList();


            return new PaginationModel { Code = 200, Msg = "success", Total = marketUnitDailyList.ToList().Count(), Result = new { Data = models } };
        }

        /// <summary>
        /// 市场价格详情(每日)
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="marketId"></param>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public ReturnValueModel MarketsDailyPriceAll(string userId, string marketId, string dateTime)
        {
            var marketPriceDailyAll = from a in _rep.Where<MarketPrice>(p => p.Status == 0 && p.CreateTime == Convert.ToDateTime(dateTime))
                                      join fi in _rep.Where<FishSpecies>(p => p.Status == 0) on a.FishId equals fi.FishCode
                                      where a.UserId == userId && a.MarketId == marketId
                                      join p in _rep.Where<MarketPrices>(p => p.Status == 0) on a.Id equals p.MarketPriceId
                                      where p.Price > 0
                                      join f in _rep.Where<FishSpecies>(p => p.Status == 0) on p.DescriptionId equals f.FishCode
                                      select new
                                      {
                                          fi.FishName,
                                          Description = f.Description.Trim(),
                                          p.Price
                                      };

            var models = marketPriceDailyAll.OrderByDescending(p => p.Description).ToList();

            return new ReturnValueModel { Code = 200, Msg = "success", Result = new { Data = models } };
        }

        /// <summary>
        /// 市场价格 鱼种
        /// </summary>
        /// <param name="dateYears">年份</param>
        /// <param name="fishName">鱼种</param>
        /// <param name="areName">地区</param>
        /// <returns></returns>
        public ReturnValueModel MarketFishYearsAll(string dateYears, string fishName, string areName, string areCode)
        {
            if (string.IsNullOrEmpty(dateYears))
                dateYears = DateTime.Now.Year.ToString();

            var fishType = fishName;
            var fishList = _rep.Where<FishSpecies>(p => p.Status == 0);
            if (string.IsNullOrEmpty(fishName))
                fishName = fishList.Where(p => p.FishName == "大黄鱼").FirstOrDefault().FishCode;
            else
                fishName = fishList.Where(p => p.FishName == fishName).FirstOrDefault().FishCode;

            //权限数据
            var sql1 = string.Empty;
            List<string> arrt3 = new List<string>();
            string[] arrt4;
            if (!string.IsNullOrEmpty(areCode) && areCode != "undefined")
            {
                sql1 = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE YUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql1, areCode).ToList();

                foreach (var item in areaAll)
                    arrt3.Add(item.YunCode);
                arrt4 = arrt3.ToArray();
            }
            else
            {
                arrt3.Add("1");
                arrt4 = arrt3.ToArray();
            }

            //地区递归
            var sql = string.Empty;
            List<string> arrt = new List<string>();
            string[] arrt2;
            if (!string.IsNullOrEmpty(areName))
            {
                sql = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE YUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql, areName).ToList();

                foreach (var item in areaAll)
                    arrt.Add(item.YunCode);
                arrt2 = arrt.ToArray();
            }
            else
            {
                arrt.Add("1");
                arrt2 = arrt.ToArray();
            }


            var fishPriceAll = /*from a in _rep.Where<FishSpecies>(p => p.Status == 0 && p.FishName == fishName)  on a.FishCode equals p.FishId*/
                               from p in _rep.Where<MarketPrice>(p => p.Status == 0)
                               where p.CreateTime.Year == Convert.ToInt32(dateYears) && p.FishId == fishName
                               join m in _rep.Where<MarketUnit>(p => p.Status == 0) on p.MarketId equals m.Id
                               join r in _rep.Where<Area>(p => p.Status == 0) on m.AreaUnCode equals r.YunCode
                               where string.IsNullOrEmpty(areName) ? r.Status == 0 : arrt2.Contains(r.YunCode)
                               && string.IsNullOrEmpty(areCode) ? r.Status == 0 : arrt4.Contains(r.YunCode)
                               select new
                               {
                                   p.FishId,
                                   p.AveragePrice,
                                   p.CreateTime
                               };

            // var total = fishPriceAll.Count();
            var fishPriceList = fishPriceAll
                                // .OrderBy(p => p.CreateTime)
                                .GroupBy(p => new
                                {
                                    Month = UtilsHelp.Condition(Convert.ToDateTime(p.CreateTime), "Month"),
                                    //  p.FishName
                                })
                                .Select(p => new
                                {
                                    Day = p.Key.Month,
                                    fishList.Where(c => c.FishCode == fishName).FirstOrDefault().FishName,
                                    // FishName = fishType,
                                    Price = Math.Round(Convert.ToDecimal(p.Average(a => a.AveragePrice)), 2)
                                });

            var total = fishPriceList.Count();
            var models = fishPriceList
                // .OrderBy(p => p.Day.Split('-')[1])
                .ToList();

            return new ReturnValueModel { Code = 200, Msg = "success", Result = new { Data = models } };
        }

        /// <summary>
        /// 市场价格
        /// </summary>
        /// <param name="index">页码</param>
        /// <param name="size">页数量</param>
        /// <param name="fishType">鱼种类别</param>
        /// <param name="fishName">鱼种名称</param>
        /// <param name="areName">地区</param>
        /// <param name="dateYears">年份</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="sasearch">模糊条件</param>
        /// <returns></returns>
        public PaginationModel MarketPriceAll(int index, int size, string fishType, string dataType, string fishName, string areName, string areCode, string dateMonth, string startDate, string endDate, string sasearch)
        {

            if (string.IsNullOrEmpty(dateMonth))
                dateMonth = UtilsHelp.Condition(DateTime.Now, "Month");

            //鱼种信息重置
            if (string.IsNullOrEmpty(fishName))
            {
                // fishType = "淡水鱼类";
                fishType = string.Empty;
                fishName = string.Empty;
            }
            else
            {
                var fishTypeAll = _rep.Where<Dictionary>(p => p.Status == 0 && p.Type == "鱼种").ToList();
                if (fishTypeAll.Where(p => p.Name == fishName).Count() > 0)
                {
                    fishType = fishName;
                    fishName = string.Empty;
                }
            }

            //权限数据
            var sql1 = string.Empty;
            List<string> arrt3 = new List<string>();
            string[] arrt4;
            if (!string.IsNullOrEmpty(areCode) && areCode != "undefined" && areCode != "null")
            {
                sql1 = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE YUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql1, areCode).ToList();

                foreach (var item in areaAll)
                    arrt3.Add(item.YunCode);
                arrt4 = arrt3.ToArray();
            }
            else
            {
                sql1 = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE PUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql1, 0).ToList();

                foreach (var item in areaAll)
                    arrt3.Add(item.YunCode);
                arrt4 = arrt3.ToArray();
            }

            //地区递归
            var sql = string.Empty;
            List<string> arrt = new List<string>();
            string[] arrt2;
            if (!string.IsNullOrEmpty(areName))
            {
                sql = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE YUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql, areName).ToList();

                foreach (var item in areaAll)
                    arrt.Add(item.YunCode);
                arrt2 = arrt.ToArray();
            }
            else
            {
                sql = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE PUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql, 0).ToList();

                foreach (var item in areaAll)
                    arrt.Add(item.YunCode);
                arrt2 = arrt.ToArray();
            }


            var test1 = from a in _rep.Where<MarketPrice>(p => p.Status == 0)
                        where a.CreateTime.Year == Convert.ToDateTime(dateMonth).Year && a.CreateTime.Month == Convert.ToDateTime(dateMonth).Month
                        join p in _rep.Where<MarketPrices>(p => p.Status == 0) on a.Id equals p.MarketPriceId
                        select new
                        {
                            MarketPriceId = a.Id,
                            //UserName = System.Web.HttpUtility.UrlDecode(u.UserName, Encoding.UTF8),
                            //fi.FishName,
                            //FishType = d.Name,
                            //m.MarketName,
                            //m.Address,
                            //r.AreaName,
                            a.AveragePrice,
                            a.UserId,
                            a.MarketId,
                            a.FishId,
                            //  Description = f.Description.Trim(),
                            p.Price,
                            a.CreateTime,
                            a.Remarks
                        };

            var test1sql = test1.ToSql();

            var test2 = from a in test1
                        join u in _rep.Where<YscUser>(p => p.Status == 0) on a.UserId equals u.Id
                        join m in _rep.Where<MarketUnit>(p => p.Status == 0) on a.MarketId equals m.Id
                        join r in _rep.Where<Area>(p => p.Status == 0) on m.AreaUnCode equals r.YunCode
                        where !string.IsNullOrEmpty(areCode) && areCode != "undefined" && areCode != "null" ? arrt4.Contains(r.YunCode) : r.Status == 0
                        select new
                        {
                            a.MarketPriceId,
                            // UserName = System.Web.HttpUtility.UrlDecode(u.UserName, Encoding.UTF8),
                            //fi.FishName,
                            //FishType = d.Name,
                            m.MarketName,
                            m.Address,
                            r.AreaName,
                            a.AveragePrice,
                            a.UserId,
                            a.MarketId,
                            a.FishId,
                            //  Description = f.Description.Trim(),
                            a.Price,
                            a.CreateTime,
                            a.Remarks
                        };

            var test2sql = test2.ToSql();

            var priceAll = from a in _rep.Where<MarketPrice>(p => p.Status == 0)
                           where a.CreateTime.Year == Convert.ToDateTime(dateMonth).Year && a.CreateTime.Month == Convert.ToDateTime(dateMonth).Month
                           join u in _rep.Where<YscUser>(p => p.Status == 0) on a.UserId equals u.Id
                           join m in _rep.Where<MarketUnit>(p => p.Status == 0) on a.MarketId equals m.Id
                           join r in _rep.Where<Area>(p => p.Status == 0) on m.AreaUnCode equals r.YunCode
                           where !string.IsNullOrEmpty(areCode) && areCode != "undefined" && areCode != "null" ? arrt4.Contains(r.YunCode) : r.Status == 0
                           //where !string.IsNullOrEmpty(areCode) && areCode != "undefined" && areCode != "null" ? arrt4.Contains(r.YunCode) : arrt2.Contains(r.YunCode)
                           //string.IsNullOrEmpty(areName) ? a.Status == 0 : arrt2.Contains(r.YunCode) &&
                           join fi in _rep.Where<FishSpecies>(p => p.Status == 0) on a.FishId equals fi.FishCode
                           join d in _rep.Where<Dictionary>(p => p.Status == 0) on fi.Type equals d.Id
                           //join p in _rep.Where<MarketPrices>(p => p.Status == 0) on a.Id equals p.MarketPriceId
                           //join f in _rep.Where<FishSpecies>(p => p.Status == 0) on p.DescriptionId equals f.FishCode
                           select new
                           {
                               a.Id,
                               UserName = System.Web.HttpUtility.UrlDecode(u.UserName, Encoding.UTF8),
                               fi.FishName,
                               FishType = d.Name,
                               m.MarketName,
                               m.Address,
                               r.AreaName,
                               a.AveragePrice,
                               // Description = f.Description.Trim(),
                               // p.Price,
                               a.CreateTime,
                               a.Remarks
                           };

            var priceList = priceAll
                // .OrderByDescending(p => p.CreateTime)
                .WhereIf(p => p.MarketName.Contains(sasearch) || p.UserName.Contains(sasearch) || p.FishName.Contains(sasearch), string.IsNullOrEmpty(sasearch) == false)
                .WhereIf(p => p.FishName == fishName, string.IsNullOrEmpty(fishName) == false)
                .WhereIf(p => p.FishType == fishType, string.IsNullOrEmpty(fishType) == false)
                .WhereIf(p => p.Remarks == dataType, string.IsNullOrEmpty(dataType) == false)
                //startDateM.AddMonths(-1)
                //.Where(p => string.IsNullOrEmpty(startDate) ? p.CreateTime >= DateTime.Now.AddMonths(-3) : p.CreateTime >= Convert.ToDateTime(startDate))
                //.Where(p => string.IsNullOrEmpty(endDate) ? p.CreateTime <= DateTime.Now : p.CreateTime <= Convert.ToDateTime(endDate))
                // .WhereIf(p => p.CreateTime >= Convert.ToDateTime(startDate), string.IsNullOrEmpty(startDate) == false)
                //.WhereIf(p => p.CreateTime <= Convert.ToDateTime(endDate), string.IsNullOrEmpty(endDate) == false)
                // .Take(8000)
                ;

            var test4 = from p in _rep.Where<MarketPrices>(p => p.Status == 0)
                        join f in _rep.Where<FishSpecies>(p => p.Status == 0) on p.DescriptionId equals f.FishCode
                        select new
                        {
                            p.MarketPriceId,
                            p.Price,
                            Description = f.Description.Trim()
                        };

            var sql2 = priceList.ToSql();
            // .OrderByDescending(p => p.CreateTime).ToSql();
            var test3 = from a in priceAll
                        select new
                        {
                            a.Id,
                            a.UserName,
                            a.FishName,
                            a.FishType,
                            a.MarketName,
                            a.Address,
                            a.AreaName,
                            a.AveragePrice,
                            Description = test4.Where(p => p.MarketPriceId == a.Id).FirstOrDefault().Description,
                            Price = test4.Where(p => p.MarketPriceId == a.Id).FirstOrDefault().Price,
                            a.CreateTime,
                            a.Remarks
                        };


            var test3sql = test3.ToSql();
            var test3Total = test3sql.Count();


            int total1 = priceAll.Count();
            int total = priceList.Count();
            // int total = 10;

            var models = test3
                // .OrderByDescending(p => p.CreateTime)
                .ToPaginationList(index, size).ToList();

            return new PaginationModel { Code = 200, Msg = "success", Total = test3Total, Result = new { Data = models } };
        }


        /// <summary>
        /// 产地价格
        /// </summary>
        /// <param name="index"></param>
        /// <param name="size"></param>
        /// <param name="areName"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="sasearch"></param>
        /// <returns></returns>
        public PaginationModel PlaceOfOriginPriceAll(int index, int size, string fishType, string dataType, string fishName, string areName, string areCode, string dateMonth, string startDate, string endDate, string sasearch)
        {
            if (string.IsNullOrEmpty(dateMonth))
                dateMonth = UtilsHelp.Condition(DateTime.Now, "Month");

            //鱼种信息重置
            if (string.IsNullOrEmpty(fishName))
            {
                fishType = string.Empty;
                fishName = string.Empty;
            }
            else
            {
                var fishTypeAll = _rep.Where<Dictionary>(p => p.Status == 0 && p.Type == "鱼种").ToList();
                if (fishTypeAll.Where(p => p.Name == fishName).Count() > 0)
                {
                    fishType = fishName;
                    fishName = string.Empty;
                }
            }

            //权限数据
            var sql1 = string.Empty;
            List<string> arrt3 = new List<string>();
            string[] arrt4;
            if (!string.IsNullOrEmpty(areCode) && areCode != "undefined" && areCode != "null")
            {
                sql1 = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE YUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql1, areCode).ToList();

                foreach (var item in areaAll)
                    arrt3.Add(item.YunCode);
                arrt4 = arrt3.ToArray();
            }
            else
            {
                //sql1 = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE PUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                //var areaAll = _rep.SqlQuery<Area>(sql1, 0).ToList();

                //foreach (var item in areaAll)
                //    arrt3.Add(item.YunCode);
                //arrt4 = arrt3.ToArray();
                arrt4 = null;
            }

            //地区递归
            var sql = string.Empty;
            List<string> arrt = new List<string>();
            string[] arrt2;
            if (!string.IsNullOrEmpty(areName))
            {
                sql = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE YUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql, areName).ToList();

                foreach (var item in areaAll)
                    arrt.Add(item.YunCode);
                arrt2 = arrt.ToArray();
            }
            else
            {
                sql = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE PUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql, 0).ToList();

                foreach (var item in areaAll)
                    arrt.Add(item.YunCode);
                arrt2 = arrt.ToArray();
            }

            var test4 = from p in _rep.Where<PlaceOfOriginPrices>(p => p.Status == 0)
                        join f in _rep.Where<FishSpecies>(p => p.Status == 0) on p.DescriptionId equals f.FishCode
                        select new
                        {
                            p.PlaceOfOriginPriceId,
                            p.Price,
                            Description = f.Description.Trim()
                        };

            var priceAll = from a in _rep.Where<PlaceOfOriginPrice>(p => p.Status == 0)
                           where a.CreateTime.Year == Convert.ToDateTime(dateMonth).Year && a.CreateTime.Month == Convert.ToDateTime(dateMonth).Month
                           join u in _rep.Where<YscUser>(p => p.Status == 0) on a.UserId equals u.Id
                           join m in _rep.Where<PlaceOfOriginUnit>(p => p.Status == 0) on a.PlaceOfOriginId equals m.Id
                           join r in _rep.Where<Area>(p => p.Status == 0) on m.AreaUnCode equals r.YunCode
                           where !string.IsNullOrEmpty(areCode) && areCode != "undefined" && areCode != "null" ? arrt4.Contains(r.YunCode) : r.Status == 0
                           //where !string.IsNullOrEmpty(areCode) && areCode != "undefined" && areCode != "null" ? arrt4.Contains(r.YunCode) : arrt2.Contains(r.YunCode)
                           join fi in _rep.Where<FishSpecies>(p => p.Status == 0) on a.FishId equals fi.FishCode
                           join d in _rep.Where<Dictionary>(p => p.Status == 0) on fi.Type equals d.Id
                           select new
                           {
                               a.Id,
                               UserName = System.Web.HttpUtility.UrlDecode(u.UserName, Encoding.UTF8),
                               fi.FishName,
                               FishType = d.Name,
                               m.PlaceOriginName,
                               m.Address,
                               r.AreaName,
                               a.AveragePrice,
                               Description = test4.Where(p => p.PlaceOfOriginPriceId == a.Id).FirstOrDefault().Description,
                               test4.Where(p => p.PlaceOfOriginPriceId == a.Id).FirstOrDefault().Price,
                               a.CreateTime,
                               a.Remarks
                           };

            var priceList = priceAll
                // .OrderByDescending(p => p.CreateTime)
                .WhereIf(p => p.PlaceOriginName.Contains(sasearch) || p.UserName.Contains(sasearch) || p.FishName.Contains(sasearch), string.IsNullOrEmpty(sasearch) == false)
                .WhereIf(p => p.FishName == fishName, string.IsNullOrEmpty(fishName) == false)
                .WhereIf(p => p.FishType == fishType, string.IsNullOrEmpty(fishType) == false)
                .WhereIf(p => p.Remarks == dataType, string.IsNullOrEmpty(dataType) == false)
                .Take(8000)
                ;

            var sql2 = priceList
                // .OrderByDescending(p => p.CreateTime)
                .ToPaginationList(index, size).ToSql();


            int total = priceList.Count();

            var models = priceList
                // .OrderByDescending(p => p.CreateTime)
                .ToPaginationList(index, size).ToList();

            return new PaginationModel { Code = 200, Msg = "success", Total = total, Result = new { Data = models } };
        }


        /// <summary>
        /// 产地价格 _鱼种
        /// </summary>
        /// <param name="dateYears"></param>
        /// <param name="fishName"></param>
        /// <param name="areName"></param>
        /// <returns></returns>
        public ReturnValueModel PlaceOfOriginPriceYearsAll(string dateYears, string fishName, string areName, string areCode)
        {
            if (string.IsNullOrEmpty(dateYears))
                dateYears = DateTime.Now.Year.ToString();

            var fishType = fishName;
            var fishList = _rep.Where<FishSpecies>(p => p.Status == 0);
            if (string.IsNullOrEmpty(fishName))
                fishName = fishList.Where(p => p.FishName == "大黄鱼").FirstOrDefault().FishCode;
            else
                fishName = fishList.Where(p => p.FishName == fishName).FirstOrDefault().FishCode;

            //权限数据
            var sql1 = string.Empty;
            List<string> arrt3 = new List<string>();
            string[] arrt4;
            if (!string.IsNullOrEmpty(areCode) && areCode != "undefined" && areCode != "null")
            {
                sql1 = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE YUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql1, areCode).ToList();

                foreach (var item in areaAll)
                    arrt3.Add(item.YunCode);
                arrt4 = arrt3.ToArray();
            }
            else
            {
                arrt3.Add("1");
                arrt4 = arrt3.ToArray();
            }

            //地区递归
            var sql = string.Empty;
            List<string> arrt = new List<string>();
            string[] arrt2;
            if (!string.IsNullOrEmpty(areName))
            {
                sql = "WITH CTE AS ( SELECT YUnCode,PUnCode FROM dbo.Area WHERE YUnCode={0} UNION ALL SELECT a.YUnCode,a.PUnCode FROM dbo.Area a INNER JOIN CTE b ON b.YUnCode=a.PUnCode) SELECT a.*  FROM dbo.Area a INNER JOIN CTE t ON a.YUnCode=t.YUnCode";
                var areaAll = _rep.SqlQuery<Area>(sql, areName).ToList();

                foreach (var item in areaAll)
                    arrt.Add(item.YunCode);
                arrt2 = arrt.ToArray();
            }
            else
            {
                arrt.Add("1");
                arrt2 = arrt.ToArray();
            }


            var fishPriceAll = /*from a in _rep.Where<FishSpecies>(p => p.Status == 0 && p.FishName == fishName)  on a.FishCode equals p.FishId*/
                               from p in _rep.Where<PlaceOfOriginPrice>(p => p.Status == 0)
                               where p.CreateTime.Year == Convert.ToInt32(dateYears)
                               join m in _rep.Where<PlaceOfOriginUnit>(p => p.Status == 0) on p.PlaceOfOriginId equals m.Id
                               join r in _rep.Where<Area>(p => p.Status == 0) on m.AreaUnCode equals r.YunCode
                               where !string.IsNullOrEmpty(areCode) && areCode != "undefined" && areCode != "null" ? arrt4.Contains(r.YunCode) : r.Status == 0
                               select new
                               {
                                   p.FishId,
                                   p.AveragePrice,
                                   p.CreateTime
                               };

            var fishPriceList = fishPriceAll
                                // .OrderBy(p => p.CreateTime)
                                .GroupBy(p => new
                                {
                                    Month = UtilsHelp.Condition(Convert.ToDateTime(p.CreateTime), "Month"),
                                    // p.FishName
                                })
                                .Select(p => new
                                {
                                    Day = p.Key.Month,
                                    fishName = fishList.Where(c => c.FishCode == fishName).FirstOrDefault().FishName,
                                    Price = Math.Round(Convert.ToDecimal(p.Average(a => a.AveragePrice)), 2)
                                }).ToList();

            var models = fishPriceList
                // .OrderBy(p => p.Day.Split('-')[1])
                .ToList();

            return new ReturnValueModel { Code = 200, Msg = "success", Result = new { Data = models } };
        }

        /// <summary>
        /// 产地价格(每日)
        /// </summary>
        /// <param name="index"></param>
        /// <param name="size"></param>
        /// <param name="areName"></param>
        /// <param name="dateTime"></param>
        /// <param name="sasearch"></param>
        /// <returns></returns>
        public PaginationModel PlaceOfOriginDailyPriceAll(int index, int size, string areName, string dateTime, string sasearch)
        {
            if (string.IsNullOrEmpty(dateTime))
                dateTime = "2019-9-12";
            // dateTime = DateTime.Now.ToString();

            if (index == 0)
            {
                index = 1; size = 10;
            }


            var placeOfOriginDailyAll = from a in _rep.Where<PlaceOfOriginPrice>(p => p.Status == 0 && p.CreateTime == Convert.ToDateTime(dateTime))
                                        join u in _rep.Where<YscUser>(p => true) on a.UserId equals u.Id
                                        join m in _rep.Where<PlaceOfOriginUnit>(p => p.Status == 0) on a.PlaceOfOriginId equals m.Id
                                        select new
                                        {
                                            a.UserId,
                                            u.UserName,
                                            PlaceOfOriginId = m.Id,
                                            UnitAddress = m.Address,
                                            m.PlaceOriginName,
                                            a.CreateTime
                                        };

            var placeOfOriginModels = placeOfOriginDailyAll
                 .OrderByDescending(p => p.CreateTime)
                .WhereIf(p => p.PlaceOriginName.Contains(sasearch) || p.UserName.Contains(sasearch), string.IsNullOrEmpty(sasearch) == false)
                .GroupBy(p => new
                {
                    p.UserName,
                    p.UserId,
                    p.PlaceOfOriginId,
                    p.UnitAddress,
                    p.PlaceOriginName,
                    Day = UtilsHelp.Condition(Convert.ToDateTime(p.CreateTime), "Day")
                })
                .Select(p => new
                {
                    p.Key.Day,
                    p.Key.UserName,
                    p.Key.UserId,
                    p.Key.PlaceOfOriginId,
                    p.Key.UnitAddress,
                    p.Key.PlaceOriginName
                });

            var models = placeOfOriginModels.ToPaginationList(index, size).ToList();

            return new PaginationModel { Code = 200, Msg = "success", Total = placeOfOriginModels.ToList().Count(), Result = new { Data = models } };
        }

        /// <summary>
        /// 产地价格详情(每日)
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="placeOfOriginId"></param>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public ReturnValueModel PlaceOfOriginsDailyPriceAll(string userId, string placeOfOriginId, string dateTime)
        {
            var marketPriceDailyAll = from a in _rep.Where<PlaceOfOriginPrice>(p => p.Status == 0 && p.CreateTime == Convert.ToDateTime(dateTime))
                                      join fi in _rep.Where<FishSpecies>(p => p.Status == 0) on a.FishId equals fi.FishCode
                                      where a.UserId == userId && a.PlaceOfOriginId == placeOfOriginId
                                      join p in _rep.Where<PlaceOfOriginPrices>(p => p.Status == 0) on a.Id equals p.PlaceOfOriginPriceId
                                      where p.Price > 0
                                      join f in _rep.Where<FishSpecies>(p => p.Status == 0) on p.DescriptionId equals f.FishCode
                                      select new
                                      {
                                          fi.FishName,
                                          Description = f.Description.Trim(),
                                          p.Price
                                      };

            var models = marketPriceDailyAll.OrderByDescending(p => p.Description).ToList();

            return new ReturnValueModel { Code = 200, Msg = "success", Result = new { Data = models } };
        }

    }
}
